package com.cloud.lawcase.api.provider;

import java.util.Date;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import com.cloud.lawcase.api.bean.LawCriteriaBean;
import com.cloud.lawcase.common.utils.DateHelper;

public class TcDicLawPageProvider {
	private static final String LIST_PAGE_SELECT = "t1.*, t2.group_name, t3.sx_name,t4.effect_level_name,t5.max_chapter_id,t5.max_section_id,t5.max_article_id,t6.law_type_name ",
			LIST_PAGE_FOMR = "tc_dic_law t1 left join tc_dic_law_group t2 on t1.group_id = t2.group_id left join tc_dic_law_sx t3 on t1.sx_id = t3.sx_id  " +
					"left join tc_dic_law_effect_level t4 on t1.xljb_id = t4.effect_level_id left join (SELECT law_id, max(chapter_id) as max_chapter_id ,max(section_id) as max_section_id,max(article_id) as max_article_id from tc_dic_law_item GROUP BY law_id) t5 on t1.law_id = t5.law_id " +
					"left join tc_dic_law_type t6 on t1.law_type_id = t6.law_type_id ";

	public String listPage(LawCriteriaBean bean) {
		SQL sql = new SQL().SELECT(LIST_PAGE_SELECT).FROM(LIST_PAGE_FOMR);
		sql.WHERE("t2.is_valid = 1");
		if (!StringUtils.isEmpty(bean.getLawTitle())) {
			bean.setLawTitle("%"+ bean.getLawTitle() +"%");
			sql.WHERE("t1.law_title like #{lawTitle}");
		}
		if (!StringUtils.isEmpty(bean.getPublishDocCode())) {
			bean.setPublishDocCode("%"+ bean.getPublishDocCode() +"%");
			sql.WHERE("t1.publish_doc_code like #{publishDocCode}");
		}
		if (bean.getSxId() > 0) {
			sql.WHERE("t1.sx_id = #{sxId}");
		}
		if(bean.getGroupId() > 0){
			sql.WHERE("t2.group_id = #{groupId}");
		}
		if (!StringUtils.isEmpty(bean.getGroupName())) {
			bean.setGroupName("%"+ bean.getGroupName() +"%");
			sql.WHERE("t2.group_name like #{groupName}");
		}
		if (!StringUtils.isEmpty(bean.getPublishDeptName())) {
			bean.setPublishDeptName("%"+ bean.getPublishDeptName() +"%");
			sql.WHERE("t1.publish_dept_names like #{publishDeptName}");
		}
		if (!StringUtils.isEmpty(bean.getApprovalDeptName())) {
			bean.setApprovalDeptName("%"+ bean.getApprovalDeptName() +"%");
			sql.WHERE("t1.approval_dept_names like #{approvalDeptName}");
		}
		if (null != bean.getApprovalDate() && bean.getApprovalDate().length > 0) {
			Date[] approvalDate = bean.getApprovalDate();
			String start, end;
			if (1 == bean.getApprovalDate().length) {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(approvalDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(approvalDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
			} else {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(approvalDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(approvalDate[1])), 5,
						DateHelper.DB_TYPE_MYSQL);
			}
			sql.WHERE("t1.approval_date between " + start + " and " + end);
		}

		if (null != bean.getPublishDate() && bean.getPublishDate().length > 0) {
			Date[] publishDate = bean.getPublishDate();
			String start, end;
			if (1 == bean.getPublishDate().length) {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(publishDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(publishDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
			} else {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(publishDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(publishDate[1])), 5,
						DateHelper.DB_TYPE_MYSQL);
			}
			sql.WHERE("t1.publish_date between " + start + " and " + end);
		}

		if (null != bean.getReleaseDate() && bean.getReleaseDate().length > 0) {
			Date[] releaseDate = bean.getReleaseDate();
			String start, end;
			if (1 == bean.getReleaseDate().length) {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(releaseDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(releaseDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
			} else {
				start = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateBegin(releaseDate[0])), 5,
						DateHelper.DB_TYPE_MYSQL);
				end = DateHelper.strToDateSql(DateHelper.dateTimeToStr(DateHelper.getDateEnd(releaseDate[1])), 5,
						DateHelper.DB_TYPE_MYSQL);
			}
			sql.WHERE("t1.release_date between " + start + " and " + end);
		}
		return sql.toString();
	}
}
